<?php 

// Include the configuration file for error management and such.
require_once ('./includes/config.inc.php'); 

// Set the page title and include the HTML header.
$page_title = 'Newnham College Associates: Find an Associate';
$site_section = 'Find';
include ('./includes/header.html');
include ('./includes/find.inc.php');
include ('./includes/fetch-user.inc.php');

require_once ('./mysql_connect.php'); // Connect to the database.

// get all the subjects that have been used by people to describe themselves
$result = fetch_used_thing_results('subject');
while ($row=mysql_fetch_array($result)) { // bung them into an array
  $used_subjects[] = $row;
}
mysql_free_result($result);

// get all the interests that have been used by people to describe themselves
$result = fetch_used_thing_results('interest');
while ($row=mysql_fetch_array($result)) { // bung them into an array
  $used_interests[] = $row;
}
mysql_free_result($result);

// get all the advice types that have been used by people to describe themselves
$result = fetch_used_thing_results('advice');
while ($row=mysql_fetch_array($result)) { // bung them into an array
  $used_advice[] = $row;
}
mysql_free_result($result);

// set up the query that'll select all of 'em
$query = "SELECT user_id, last_name FROM user_details WHERE is_active = 1 ORDER BY last_name, first_name";
$do_search= FALSE;
$do_criteria = FALSE;

if (isset($_POST['submitted'])) { // Handle the  submitted form.
  $tables = " user_details";
  $join = " WHERE user_details.is_active = 1 AND ";
  $ins = " ";

  $in_subjects = get_in_where_statement('subject');
  $do_subjects = ($in_subjects != '');
  $in_interests = get_in_where_statement('interest');
  $do_interests = ($in_interests != '');
  $in_advices = get_in_where_statement('advice');
  $do_advices = ($in_advices != '');

  if ($do_subjects) {
      $chosen_subjects = get_chosen_things('subject', $in_subjects);
      $do_search = TRUE;
      $do_criteria = TRUE;
  }
  if ($do_interests) {
      $chosen_interests = get_chosen_things('interest', $in_interests);
      $do_search = TRUE;
      $do_criteria = TRUE;
  }
  if ($do_advices) {
      $chosen_advices = get_chosen_things('advice', $in_advices);
      $do_search = TRUE;
      // advice doesn't count as a criterion here
  }

  if ($do_search) {

      if ($do_criteria) {
          $ins .= " ( ";
      }
      if ($do_subjects) {
          $ins .= "  user_subject.subject_id IN {$in_subjects}";
          $tables .= ", user_subject";
          $join .= " user_details.user_id = user_subject.user_id AND ";
      }
      if ($do_interests) {
          if ($do_subjects) {
              $ins .= $_POST['con_disjunct'];
          }
          $ins .= "  user_interest.interest_id IN {$in_interests}";
          $tables .= ", user_interest";
          $join .= " user_details.user_id = user_interest.user_id AND ";
      }
      if ($do_criteria) {
          $ins .= " ) ";
      }
      if ($do_advices) {
          if ($do_criteria) {
              $ins .= " AND ";
          }
          $ins .= "  user_advice.advice_id IN {$in_advices}";
          $tables .= ", user_advice";
          $join .= " user_details.user_id = user_advice.user_id AND ";
      }

      $query = "SELECT user_details.user_id FROM $tables 
                $join $ins 
                GROUP BY user_details.last_name, user_details.first_name ";
  }
} 

$result = mysql_query($query)  or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
$user_ids=null;
while ($row=mysql_fetch_array($result)) { // bung them into an array
  $user_ids[] = $row[0];
}
// we've now got the list of users that have been selected by the search criteria
mysql_free_result($result);


echo "<h1 >Find an Associate</h1>\n";

echo "<p>The Associates represent a wide range of skills, career experience and general life choices, and can be contacted by email through this page. Current&mdash;and many former&mdash;Associates are happy to offer general and practical advice on a one-off or continuing basis; you are welcome to email more than one Associate with the same query.</p>\n";

echo "<p>Associates offer individual advice and help in finding work experience, choosing or changing career, and practical help on presenting a good CV and interview technique. We also offer networking opportunities, and contacts for those working or travelling abroad.</p>\n";

echo "<p>Use this page to find an Associate who you think may be able to help you. Try the <a href=\"#choose\">keyword search</a> (at the bottom of the page) or simply use your browser to search for words in the text. If you can't find a specific Associate who you think may be able to help you, please <a href=\"sendMessage.php?assoc=sec\">send your query to the Secretary</a>.</p>";

$number = count($user_ids);  // how many did we find?
if ($do_search) {
  echo "<p class=\"center\">$number Associates were found (shown below)&nbsp;&bull;&nbsp; <a href=\"search.php\">Show all Associates</a> &nbsp;&bull;&nbsp; <a href=\"#choose\">Change search conditions</a></p>";
  echo "<p>Search conditions:</p>\n<ul>";
  if ($do_subjects) {
      output_chosen_things($chosen_subjects, 'subjects');
  }
  if ($do_interests) {
      if ($do_subjects) {
          echo "<p>{$_POST['con_disjunct']}</p>";
      }
      output_chosen_things($chosen_interests, 'occupations or areas of expertise');
  }
  if ($do_advices) {
      if ($do_criteria) {
      if ($do_subjects) {
          echo "<p>AND</p>";
      }
      }
      output_chosen_things($chosen_advices, 'types of advice');
  }
  echo "</ul>\n";
} else {
  echo "<p class=\"center\">Showing all Associates &nbsp;&bull;&nbsp; <a href=\"#choose\">Change search conditions</a></p>";
}

echo "<h2>Results</h2>\n";

if ($number >0) 
{
    $first = TRUE;
    foreach ($user_ids as $user_id) { 
        if ($first) {
            $first= FALSE;
            echo "<hr />";
        }
        output_user_info($user_id);
        echo "<hr />";
    }
}
else {
    echo "<p>No associates were found.</p>\n";
}
?>
<a name="choose" id="choose"></a><h2>Search conditions</h2>

<p>Use the control key to make multiple selections.</p>

<form action="search.php" method="post">
<table>
<tr><td>Subjects read at university (under- or postgraduate)</td>
<td>&nbsp;</td>
    <td>Occupation and areas of expertise</td>
</tr>
<tr><td><select name="subject[]" size="15" multiple="true">
            <option value="0">ANY</option>
        <?php 
        foreach ($used_subjects as $subj) {
          echo "<option value=\"$subj[0]\">$subj[1]</option>";
        }
         ?>
        </select>
    </td>
        <td>
Find Associates with any of the specified subjects<br />
        <input type="radio" name="con_disjunct" value="AND" /> AND<br />
        <input type="radio" name="con_disjunct" value="OR" checked="TRUE"/> OR<br />
any of the specified interests
        </td>
    <td><select name="interest[]" size="15" multiple="true">
            <option value="0">ANY</option>
        <?php 
        foreach ($used_interests as $subj) {
          echo "<option value=\"$subj[0]\">$subj[1]</option>";
        }
         ?>
        </select>
    </td>
</tr>
<tr>
    <td>&nbsp;</td><td>AND who offer any of these types of advice</td><td>&nbsp;</td>
</tr>
<tr>
    <td>&nbsp;</td><td><select name="advice[]" size="5" multiple="true">
            <option value="0">ANY</option>
        <?php 
        foreach ($used_advice as $subj) {
          echo "<option value=\"$subj[0]\">$subj[1]</option>";
        }
         ?>
        </select>
    </td><td>&nbsp;</td>
</tr>
</table>

        <div align="center"  style="margin-top: 1em;"><input type="submit" name="submit" value="Search" /></div>
<input type="hidden" name="submitted" value="TRUE" />
</form>


<?php 

mysql_close(); // Close the database connection. 

// Include the HTML footer file.
include ('./includes/footer.html');
?>